﻿using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using GenaralLibs;

namespace Lists
{
    public class FileDinhKem : DoiTuong
    {
        #region int ID property
        public int ID
        {
            get
            {
                return (int)_DataRow["ID"];
            }
            set
            {
                _DataRow["ID"] = value;
            }
        }
        #endregion

        #region int LoaiGiayTo_NganhNgheID property
        public int LoaiGiayTo_NganhNgheID
        {
            get
            {
                return (int)_DataRow["LoaiGiayTo_NganhNgheID"];
            }
            set
            {
                _DataRow["LoaiGiayTo_NganhNgheID"] = value;
            }
        }
        #endregion

        #region int HoSoID property
        public int HoSoID
        {
            get
            {
                return (int)_DataRow["HoSoID"];
            }
            set
            {
                _DataRow["HoSoID"] = value;
            }
        }
        #endregion

        #region string TenFile property
        public string TenFile
        {
            get
            {
                return (string)_DataRow["TenFile"];
            }
            set
            {
                _DataRow["TenFile"] = value;
            }
        }
        #endregion

        #region int KichThuocFile property
        public int KichThuocFile
        {
            get
            {
                return (int)_DataRow["KichThuocFile"];
            }
            set
            {
                _DataRow["KichThuocFile"] = value;
            }
        }
        #endregion

        #region byte[] FileDK property
        public byte[] FileDK
        {
            get
            {
                return (byte[])_DataRow["FileDK"];
            }
            set
            {
                _DataRow["FileDK"] = value;
            }
        }
        #endregion

        #region string GhiChu property
        public string GhiChu
        {
            get
            {
                return (string)_DataRow["GhiChu"];
            }
            set
            {
                _DataRow["GhiChu"] = value;
            }
        }
        #endregion

        #region string Text1 property
        public string Text1
        {
            get
            {
                return "Cập nhật";
            }
        }
        #endregion

        #region string Text2 property
        public string Text2
        {
            get
            {
                return "Xem";
            }
        }
        #endregion

        public FileDinhKem(DataRow dataRow)
            : base(dataRow)
        {
        }

        public override void InsertToDB()
        {
            SqlConnection con = new SqlConnection(GenaralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                InsertToDB(cmd);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GenaralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }
        public override void UpdateToDB()
        {
            SqlConnection con = new SqlConnection(GenaralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                UpdateToDB(cmd);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(GenaralLibs.CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
            finally
            {
                con.Close();
            }
        }

        public override void InsertToDB(SqlCommand cmd)
        {
            if (cmd.Connection.State != ConnectionState.Open) throw new CustomException("Chưa mở connection");
            string values = ""; string listOfColumns = "";
            cmd.Parameters.Clear();
            foreach (DataColumn column in this.DataRow.Table.Columns)
            {
                if (column.ColumnName != "LastUpdated" && column.ColumnName != "DiaDiemKD" && column.ColumnName != "Text1" && column.ColumnName != "Text2") //Vì DiaDiemKD chỉ là cột ảo để hiển thị trong object
                {
                    values = values + "@" + column.ColumnName + ", ";
                    listOfColumns = listOfColumns + column.ColumnName + ", ";
                    cmd.Parameters.AddWithValue(column.ColumnName, this.DataRow[column.ColumnName]);
                }
            }

            if (values.EndsWith(", ")) values = values.Substring(0, values.Length - 2);
            if (listOfColumns.EndsWith(", ")) listOfColumns = listOfColumns.Substring(0, listOfColumns.Length - 2);

            cmd.CommandText = string.Format("SET IDENTITY_INSERT {0} ON \n" +
                                            "INSERT INTO [{0}]({1}) VALUES({2}) \n" +
                                            "SET IDENTITY_INSERT {0} OFF",
                                            this.DataRow.Table.TableName, listOfColumns, values);
            cmd.ExecuteNonQuery();
            if (this.DataRow.Table.Rows.IndexOf(this.DataRow) < 0) this.DataRow.Table.Rows.Add(this.DataRow);
        }
        public override void UpdateToDB(SqlCommand cmd)
        {
            if (cmd.Connection.State != ConnectionState.Open) throw new CustomException("Chưa mở connection");
            string values = "";
            cmd.Parameters.Clear();
            foreach (DataColumn column in this.DataRow.Table.Columns)
            {
                if (column.ColumnName != "LastUpdated" && column.ColumnName != "ID" && column.ColumnName != "DiaDiemKD" && column.ColumnName != "Text1" && column.ColumnName != "Text2") //Vì DiaDiemKD chỉ là cột ảo để hiển thị trong object
                {
                    values += string.Format("{0} = @{0}, ", column.ColumnName);
                    if (this.DataRow[column.ColumnName] is int && (int)this.DataRow[column.ColumnName] == 0)
                    {
                        cmd.Parameters.AddWithValue(column.ColumnName, DBNull.Value);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(column.ColumnName, this.DataRow[column.ColumnName]);
                    }
                }
            }

            if (values.EndsWith(", ")) values = values.Substring(0, values.Length - 2);

            cmd.CommandText = string.Format("SET IDENTITY_INSERT {0} ON \n" +
                                            "UPDATE [{0}] SET {1} WHERE HoSoID = {2} AND LoaiGiayTo_NganhNgheID = {3}\n" +
                                            "SET IDENTITY_INSERT {0} OFF",
                                            this.DataRow.Table.TableName,
                                            values,
                                            this.DataRow["HoSoID"],
                                            this.DataRow["LoaiGiayTo_NganhNgheID"]);
            cmd.ExecuteNonQuery();
        }
    }

    public class DanhMucFileDinhKem : DanhMucDoiTuong, IEnumerable
    {
        #region Instance property: Singleton Design pattern
        private static DanhMucFileDinhKem _Instance;
        public static DanhMucFileDinhKem Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new DanhMucFileDinhKem();
                    _Instance.DataView = _Instance.DataTable.DefaultView;
                }
                return _Instance;
            }
        }
        #endregion

        #region override TableName property
        public override string TableName
        {
            get
            {
                return "FileDinhKem";
            }
        }
        #endregion

        #region Methods required by IEnumerable (use for foreach)
        public IEnumerator GetEnumerator()
        {
            return new TokenEnumerator(this);
        }

        private class TokenEnumerator : IEnumerator
        {
            private int _Position = -1;
            DanhMucDoiTuong _Dstk;

            public TokenEnumerator(DanhMucDoiTuong dstk)
            {
                _Dstk = dstk;
            }

            // Declare the MoveNext method required by IEnumerator:
            public bool MoveNext()
            {
                if (_Position < _Dstk.DataView.Count - 1)
                {
                    _Position++;
                    return true;
                }
                else
                {
                    return false;
                }
            }

            // Declare the Reset method required by IEnumerator:
            public void Reset()
            {
                _Position = -1;
            }

            // Declare the Current property required by IEnumerator:
            public object Current
            {
                get
                {
                    return new FileDinhKem((_Dstk.DataView[_Position].Row));
                }
            }
        }
        #endregion

        public FileDinhKem this[int index]
        {
            get
            {
                if (0 <= index && index <= this.Count)
                {
                    return new FileDinhKem(DataTable.Rows[index]);
                }
                else
                {
                    return null;
                }
            }
        }

        public FileDinhKem NewDoiTuong()
        {
            try
            {
                return new FileDinhKem(Instance.DataTable.NewRow());
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public FileDinhKem GetByProperty(string property, object value)
        {
            try
            {
                DataRow[] rows = GetDataRows(property, value);
                if (rows.Length > 0) return new FileDinhKem(rows[0]);
                else return null;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public FileDinhKem GetByID(int objID)
        {
            try
            {
                return GetByProperty("ID", objID);
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DanhMucFileDinhKem Filter(string filterExpression)
        {
            try
            {
                DanhMucFileDinhKem obj = new DanhMucFileDinhKem();
                obj.DataView = new DataView(DanhMucFileDinhKem.Instance.DataTable, filterExpression, "", DataViewRowState.CurrentRows);
                return obj;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public DanhMucFileDinhKem GetByHoSoID(int hoSoID)
        {
            try
            {
                return Filter(string.Format("HoSoID = {0}", hoSoID));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public FileDinhKem GetByHoSoID_LoaiHoSoNganhNgheIDExisted(int hoSoID, int loaiHoSo_NganhNgheID)
        {
            try
            {
                DanhMucFileDinhKem dm = Filter(string.Format("HoSoID = {0} AND LoaiGiayTo_NganhNgheID = {1}", hoSoID, loaiHoSo_NganhNgheID));
                if (dm.Count >= 1) return dm[0];
                else return null;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public static DanhMucFileDinhKem GetAll()
        {
            try
            {
                return Instance;
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
    }
}